const { getConnection } = require('../config/db');

class CategoryModel {
  // 获取所有区域
  static async getAllAreas() {
    const connection = await getConnection();
    try {
      const sql = `
        SELECT 
          a.id,
          a.name,
          a.level,
          a.parent_id,
          a.code,
          a.created_at,
          a.updated_at,
          COUNT(DISTINCT va.video_id) as video_count
        FROM areas a
        LEFT JOIN video_areas va ON a.id = va.area_id
        GROUP BY a.id, a.name, a.level, a.parent_id, a.code, a.created_at, a.updated_at
        ORDER BY a.created_at DESC
      `;
      const [rows] = await connection.execute(sql);
      return rows;
    } finally {
      connection.release();
    }
  }

  // 获取所有剧种
  static async getAllGenres() {
    const connection = await getConnection();
    try {
      const sql = `
        SELECT 
          g.id,
          g.name,
          g.created_at,
          g.updated_at,
          COUNT(vg.video_id) as video_count
        FROM genres g
        LEFT JOIN video_genres vg ON g.id = vg.genre_id
        GROUP BY g.id, g.name, g.created_at, g.updated_at
        ORDER BY g.created_at DESC
      `;
      const [rows] = await connection.execute(sql);
      return rows;
    } finally {
      connection.release();
    }
  }

  // 创建区域
  static async createArea(name) {
    const connection = await getConnection();
    try {
      const sql = 'INSERT INTO areas (name) VALUES (?)';
      const [result] = await connection.execute(sql, [name]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 创建剧种
  static async createGenre(name) {
    const connection = await getConnection();
    try {
      const sql = 'INSERT INTO genres (name) VALUES (?)';
      const [result] = await connection.execute(sql, [name]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 更新区域
  static async updateArea(id, name) {
    const connection = await getConnection();
    try {
      const sql = 'UPDATE areas SET name = ? WHERE id = ?';
      const [result] = await connection.execute(sql, [name, id]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 更新剧种
  static async updateGenre(id, name) {
    const connection = await getConnection();
    try {
      const sql = 'UPDATE genres SET name = ? WHERE id = ?';
      const [result] = await connection.execute(sql, [name, id]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 删除区域
  static async deleteArea(id) {
    const connection = await getConnection();
    try {
      const sql = 'DELETE FROM areas WHERE id = ?';
      const [result] = await connection.execute(sql, [id]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 删除剧种
  static async deleteGenre(id) {
    const connection = await getConnection();
    try {
      const sql = 'DELETE FROM genres WHERE id = ?';
      const [result] = await connection.execute(sql, [id]);
      return result;
    } finally {
      connection.release();
    }
  }
}

module.exports = CategoryModel;